<HTML>
<HEAD>
<NAME>SQL-relay TCL module</NAME>
</HEAD>

<BODY BGCOLOR="#FFFFFF" TEXT="#000000">
<H1>TCL API</H1>
<HR>
<UL>
<LI><A HREF="#USING">USING</A>
<LI><A HREF="#FUNCTIONS">FUNCTIONS</A>
</UL>
<H3><A NAME="USING">USING</H3>
You can use the module by loading it in your TCL script
and calling SQL Relay functions.
<P>
For example:
<PRE>
load /usr/lib/sqlrelay/sqlrelay.so sqlrelay
set con [sqlrcon -server "adasz" -port "9000" -user "user1" -password "password"]
set cur [$con sqlrcur]
catch [$cur sendQuery "select table_name from user_tables"]
$con endSession
for {set i 0} {$i&#60;[$cur rowCount]} {incr i} {
  puts [$cur getFieldByName $i "table_name"]
}
</PRE>
<HR>
<H3><A NAME="FUNCTIONS">FUNCTIONS</H3>
<UL>
<LI>
<CODE>
set con [sqlrcon -server "server" -port "port" -socket "socket"
		-user "user" -password "password" -retrytime "0" -tries "1"]
</CODE>
<P>
Initiates a connection to "server" on "port"
or to the unix "socket" on the local machine
and authenticates with "user" and "password".
Failed connections will be retried for 
"tries" times on interval "retrytime".
If "tries" is 0 then retries will continue
forever.  If "retrytime" is 0 then retries
will be attempted on a default interval.
</P>
<P>
If the "socket" parameter isn't "" then an
attempt will be made to 
connect through it before attempting to 
connect to "server" on "port".  If it is 
"" then no attempt will be made to 
connect through the socket.
<P>
<LI>
<CODE>
$con endSession
</CODE>
<P>
terminates the session
<P>
<LI>
<CODE>
$con suspendSession
</CODE>
<P>
Disconnects this client from the current
session but leaves the session open so 
that another client can connect to it 
using resumeSession
<P>
<LI>
<CODE>
$con getConnectionPort
</CODE>
<P>
Returns the inet port that the client is 
communicating over. This parameter may be 
passed to another client for use in
the resumeSession command.
Note: the value returned by this method is
only valid after a call to suspendSession.
<P>
<LI>
<CODE>
$con getConnectionSocket
</CODE>
<P>
Returns the unix socket that the client is 
communicating over. This parameter may be 
passed to another client for use in
the resumeSession command.
Note: the value returned by this method is
only valid after a call to suspendSession.
<P>
<LI>
<CODE>
$con resumeSession port socket
</CODE>
<P>
Resumes a session previously left open 
using suspendSession.
Returns 1 on success and 0 on failure.
<P>
<LI>
<CODE>
$con ping
</CODE>
<P>
Returns 1 if the database is up and 0
if it's down.
<P>
<LI>
<CODE>
$con identify
</CODE>
<P>
Returns the type of database: 
oracle8, postgresql, mysql, etc.
<P>
<LI>
<CODE>
$con dbVersion
</CODE>
<P>
Returns the version of the database
<P>
<P>
<LI>
<CODE>
$con serverVersion
</CODE>
<P>
Returns the version of the SQL Relay server software
<P>
<P>
<LI>
<CODE>
$con clientVersion
</CODE>
<P>
Returns the version of the SQL Relay client software
<P>
<LI>
<CODE>
$con bindFormat
</CODE>
<P>
Returns a string representing the format
of the bind variables used in the db.
<P>
<LI>
<CODE>
$con autoCommit true|false
</CODE>
<P>
Instructs the database to wait for the 
client to tell it when to commit.
<P>
<LI>
<CODE>
$con commit
</CODE>
<P>
Issues a commit. Returns 1 if the commit succeeded,
0 if it failed and -1 if an error occurred.
<P>
<LI>
<CODE>
$con rollback
</CODE>
<P>
Issues a rollback. Returns 1 if the rollback succeeded,
0 if it failed and -1 if an error occurred.
<P>
<LI>
<CODE>
$con debug true|false
</CODE>
<P>
Causes verbose debugging information to be
sent to standard output.  Another way to do
this is to start a query with "-- debug\n".
<P>
<LI>
<CODE>
$con getDebug
</CODE>
<P>
returns FALSE if debugging is off and TRUE if
debugging is on
<P>
<LI>
<CODE>
$con sqlrcur
</CODE>
<P>
Creates a cursor object
<p>
<LI>
<CODE>
$con eval query
</CODE>
<P>
Runs query and returns the result set as a list of lists
where the main list contains rows and each sub-list contains
the fields of each row.
<p>
<LI>
<CODE>
$cur setResultSetBufferSize rows
</CODE>
<P>
Sets the number of rows of the result set
to buffer at a time.  0 (the default)
means buffer the entire result set.
<P>
<LI>
<CODE>
$cur getResultSetBufferSize
</CODE>
<P>
Returns the number of result set rows that 
will be buffered at a time or 0 for the
entire result set.
<P>
<LI>
<CODE>
$cur dontGetColumnInfo
</CODE>
<P>
Tells the server not to send any column
info (names, types, sizes).  If you don't
need that info, you should call this
method to improve performance.
<P>
<LI>
<CODE>
$cur caseColumnNames upper|lower|mixed
</CODE>
<P>
Columns names are converted to upper or lower case
or returned as they appear in the database.
<P>
<CODE>
$cur getColumnInfo
</CODE>
<P>
Tells the server to send column info.
<P>
<LI>
<CODE>
$cur cacheToFile filename
</CODE>
<P>
Sets query caching on.  Future queries
will be cached to the file "filename".

A default time-to-live of 10 minutes is
also set.

Note that once cacheToFile is called,
the result sets of all future queries will
be cached to that file until another call 
to cacheToFile() changes which file to
cache to or a call to cacheOff turns off
caching.
<P>
<LI>
<CODE>
$cur setCacheTtl ttl
</CODE>
<P>
Sets the time-to-live for cached result
sets. The sqlr-cachemanger will remove each 
cached result set "ttl" seconds after it's 
created.
<P>
<LI>
<CODE>
$cur getCacheFileName
</CODE>
<P>
Returns the name of the file containing the
most recently cached result set.
<P>
<LI>
<CODE>
$cur cacheOff
</CODE>
<P>
Sets query caching off.
<P>
</UL>
<P>
If you don't need to use substitution or bind variables
in your queries, use these two methods.
<P>
<UL>
<LI>
<CODE>
$cur sendQuery query
</CODE>
<P>
Sends "query" and gets a return set.
Returns TRUE on success and FALSE on failure.
<P>
<LI>
<CODE>
$cur sendQueryWithLength query length
</CODE>
<P>
Sends "query" with length "length" and gets
a result set. This method must be used if
the query contains binary data.
<P>
<LI>
<CODE>
$cur sendFileQuery path filename
</CODE>
<P>
Sends the query in file "path"/"filename"
and gets a return set.
Returns TRUE on success and FALSE on failure.
<P>
</UL>
<P>
If you need to use substitution or bind variables, in your
queries use the following methods.  See the API documentation
for more information about substitution and bind variables.
<P>
<UL>
<LI>
<CODE>
$cur prepareQuery query
</CODE>
<P>
Prepare to execute "query".
<P>
<LI>
<CODE>
$cur prepareQueryWithLength query length
</CODE>
<P>
Prepare to execute "query" with length 
"length".  This method must be used if the
query contains binary data.
<P>
<LI>
<CODE>
$cur prepareFileQuery path filename
</CODE>
<P>
Prepare to execute the contents of "path"/"filename".
<P>
<LI>
<CODE>
$cur substitution variable value ?precision? ?scale?
</CODE>
<P>
Define a substitution variable.
<P>
<LI>
<LI>
<CODE>
$cur substitutions {{variable value ?precision? ?scale?} ...}
</CODE>
<P>
Define a set of substitution variables.
<P>
<CODE>
$cur clearBinds
</CODE>
<P>
Clear all bind variables.
<P>
<LI>
<CODE>
$cur countBindVariables
</CODE>
<P>
Parses the previously prepared query,
counts the number of bind variables defined
in it and returns that number.
<P>
<LI>
<CODE>
$cur inputBind variable value ?precision? ?scale?
<BR>
$cur inputBindBlob variable length
<BR>
$cur inputBindClob variable length
</CODE>
<P>
Define an input bind variable.
<P>
<LI>
<CODE>
$cur inputBinds {{variable value ?precision? ?scale?} ...}
</CODE>
<P>
Defines a set of input bind variables.
<P>
<LI>
<CODE>
$cur defineOutputBindString variable length
</CODE>
<P>
Define a string output bind variable.
"length" bytes will be reserved to store the value.
<P>
<LI>
<CODE>
$cur defineOutputBindInteger variable
</CODE>
<P>
Define an integer output bind variable.
<P>
<LI>
<CODE>
$cur defineOutputBindDouble variable
</CODE>
<P>
Define a double precision floating point output bind variable.
<P>
<LI>
<CODE>
$cur defineOutputBindBlob variable
</CODE>
<P>
Define a BLOB output bind variable.
<P>
<LI>
<CODE>
$cur defineOutputBindClob variable
</CODE>
<P>
Define a CLOB output bind variable.
<P>
<LI>
<CODE>
$cur defineOutputBindCursor variable
</CODE>
<P>
Define a cursor output bind variable.
<P>
<LI>
<CODE>
$cur validateBinds
</CODE>
<P>
If you are binding to any variables that might not actually be in your query, 
call this to ensure that the database won't try to bind them unless they really
are in the query.
<P>
<LI>
<CODE>
$cur validBind variable
</CODE>
<P>
Returns true if "variable" was a valid bind variable of the query.
<P>
<LI>
<CODE>
$cur executeQuery
</CODE>
<P>
Execute the query that was previously prepared and bound.
<P>
<LI>
<CODE>
$cur fetchFromBindCursor
</CODE>
<P>
Fetch from a cursor that was returned as an output bind variable.
<P>
<LI>
<CODE>
$cur getOutputBindString variable
</CODE>
<P>
Get the value stored in a previously defined output bind variable.
<P>
<LI>
<CODE>
$cur getOutputBindBlob variable
</CODE>
<P>
Get the value stored in a previously defined output bind variable.
<P>
<LI>
<CODE>
$cur getOutputBindClob variable
</CODE>
<P>
Get the value stored in a previously defined output bind variable.
<P>
<LI>
<CODE>
$cur getOutputBindInteger variable
</CODE>
<P>
Get the value stored in a previously defined output bind variable
and return it as an integer.
<P>
<LI>
<CODE>
$cur getOutputBindDouble variable
</CODE>
<P>
Get the value stored in a previously defined output bind variable
and return it as a double.
<P>
<LI>
<CODE>
$cur getOutputBindLength variable
</CODE>
<P>
Get the length of the value stored in a previously defined output bind variable.
<P>
<LI>
<CODE>
$cur getOutputBindCursor variable
</CODE>
<P>
Get the cursor associated with a previously defined output bind variable.
<P>
<LI>
<CODE>
$cur openCachedResultSet filename
</CODE>
<P>
Opens a cached result set as if a query that
would have generated it had been executed.
Returns TRUE on success and FALSE on failure.
<P>
<LI>
<CODE>
$cur colCount
</CODE>
<P>
returns the number of columns in the current
return set
<P>
<LI>
<CODE>
$cur rowCount
</CODE>
<P>
returns the number of rows in the current
return set
<P>
<LI>
<CODE>
$cur totalRows
</CODE>
<P>
Returns the total number of rows that will 
be returned in the result set.  Not all 
databases support this call.  Don't use it 
for applications which are designed to be 
portable across databases.  -1 is returned
by databases which don't support this option.
<P>
<LI>
<CODE>
$cur affectedRows
</CODE>
<P>
Returns the number of rows that were 
updated, inserted or deleted by the query.
Not all databases support this call.  Don't 
use it for applications which are designed 
to be portable across databases.  -1 is 
returned by databases which don't support 
this option.
<P>
<LI>
<CODE>
$cur firstRowIndex
</CODE>
<P>
Returns the index of the first buffered row.
This is useful when buffering only part of
the result set at a time.
<P>
<LI>
<CODE>
$cur endOfResultSet
</CODE>
<P>
Returns 0 if part of the result set is still
pending on the server and 1 if not.  This
method can only return 0 if 
$cur setResultSetBufferSize has been called
with a parameter other than 0.
<P>
<LI>
<CODE>
$cur errorMessage
</CODE>
<P>
If a query failed and generated an error, the
error message is available here.  If the
query succeeded then this method returns FALSE
<P>
<LI>
<CODE>
$cur getFieldByName row col
<br>
$cur getFieldByIndex row col
</CODE>
<P>
returns a string with value of the
specified row and column
<P>
<LI>
<CODE>
$cur getFieldAsIntegerByName row col
<br>
$cur getFieldAsIntegerByIndex row col
</CODE>
<P>
returns an integer with value of the
specified row and column
<P>
<LI>
<CODE>
$cur getFieldAsDoubleByName row col
<br>
$cur getFieldAsDoubleByIndex row col
</CODE>
<P>
returns a double with value of the
specified row and column
<P>
<LI>
<CODE>
$cur getFieldLengthByName row col
<br>
$cur getFieldLengthByIndex row col
</CODE>
<P>
returns the length of the
specified row and column
<P>
<LI>
<CODE>
$cur getRow row
</CODE>
<P>
returns an indexed array of the
values of the specified row
<P>
<LI>
<CODE>
$cur getRowLengths row
</CODE>
<P>
returns an indexed array of the
lengths of the specified row
<P>
<LI>
<CODE>
$cur getColumnNames
</CODE>
<P>
returns the array of the
column names of the current return set
<P>
<LI>
<CODE>
$cur getColumnName col
</CODE>
<P>
returns the name of the specified column
<P>
<LI>
<CODE>
$cur getColumnTypeByName col
<br>
$cur getColumnTypeByIndex col
</CODE>
<P>
returns the type of the specified column
<P>
<LI>
<CODE>
$cur getColumnLengthByName col
<br>
$cur getColumnLengthByIndex col
</CODE>
<P>
returns the length of the specified column.
<P>
<LI>
<CODE>
$cur getColumnPrecisionByName col
<br>
$cur getColumnPrecisionByIndex col
</CODE>
<P>
Returns the precision of the specified
column.
Precision is the total number of digits in
a number.  eg: 123.45 has a precision of 5.
For non-numeric types, it's the number of
characters in the string.
<P>
<LI>
<CODE>
$cur getColumnScaleByName col
<br>
$cur getColumnScaleByIndex col
</CODE>
<P>
Returns the scale of the specified column.
Scale is the total number of digits to the
right of the decimal point in a number.
eg: 123.45 has a scale of 2.
<P>
<LI>
<CODE>
$cur getColumnIsNullableByName col
<br>
$cur getColumnIsNullableByIndex col
</CODE>
<P>
Returns 1 if the specified column can
contain nulls and 0 otherwise.
<P>
<LI>
<CODE>
$cur getColumnIsPrimaryKeyByName col
<br>
$cur getColumnIsPrimaryKeyByIndex col
</CODE>
<P>
Returns 1 if the specified column is a
primary key and 0 otherwise.
<P>
<LI>
<CODE>
$cur getColumnIsUniqueByName col
<br>
$cur getColumnIsUniqueByIndex col
</CODE>
<P>
Returns 1 if the specified column is
unique and 0 otherwise.
<P>
<LI>
<CODE>
$cur getColumnIsPartOfKeyByName col
<br>
$cur getColumnIsPartOfKeyByIndex col
</CODE>
<P>
Returns 1 if the specified column is
part of a composite key and 0 otherwise.
<P>
<LI>
<CODE>
$cur getColumnIsUnsignedByName col
<br>
$cur getColumnIsUnsignedByIndex col
</CODE>
<P>
Returns 1 if the specified column is
an unsigned number and 0 otherwise.
<P>
<LI>
<CODE>
$cur getColumnIsZeroFilledByName col
<br>
$cur getColumnIsZeroFilledByIndex col
</CODE>
<P>
Returns 1 if the specified column was
created with the zero-fill flag and 0
otherwise.
<P>
<LI>
<CODE>
$cur getColumnIsBinaryByName col
<br>
$cur getColumnIsBinaryByIndex col
</CODE>
<P>
Returns 1 if the specified column
contains binary data and 0
otherwise.
<P>
<LI>
<CODE>
$cur getColumnIsAutoIncrementByName col
<br>
$cur getColumnIsAutoIncrementByIndex col
</CODE>
<P>
Returns 1 if the specified column
auto-increments and 0 otherwise.
<P>
<LI>
<CODE>
$cur getLongestByName col
<br>
$cur getLongestByIndex col
</CODE>
<P>
Returns the length of the longest field in the
specified column.
<P>
<LI>
<CODE>
$cur suspendResultSet
</CODE>
<P>
Tells the server to leave this result
set open when the connection calls 
suspendSession so that another connection can 
connect to it using resumeResultSet after 
it calls resumeSession.
<P>

<LI>
<CODE>
$cur getResultSetId
</CODE>
<P>
Returns the internal ID of this result set.
This parameter may be passed to another 
statement for use in the resumeResultSet
method.
Note: the value returned by this method is
only valid after a call to suspendResultSet.
<P>
<LI>
<CODE>
$cur resumeResultSet id
</CODE>
<P>
Resumes a result set previously left open 
using suspendSession.
Returns 1 on success and 0 on failure.
<P>
<LI>
<CODE>
$cur resumeCachedResultSet id filename
</CODE>
<P>
Resumes a result set previously left open
using suspendSession and continues caching
the result set to "filename".
Returns 1 on success and 0 on failure. 
<P>
</UL>
<HR>
</BODY>
</HTML>
